package cn.hairui.blog.dao;

import cn.hairui.blog.entity.Tag;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 标签表 Mapper 接口
 * </p>
 *
 * @author laoganbu
 * @since 2020-04-06
 */
@Mapper
public interface TagMapper extends BaseMapper<Tag> {

    @Select("select count(1) from tag")
    int count();

    @Select("select\n" +
            "  t1.id,\n" +
            "  t1.name,\n" +
            "  count(1) as count\n" +
            "from tag t1\n" +
            "  inner join articaltag t2\n" +
            "    on t1.id = t2.tag_id\n" +
            "  inner join artical t3\n" +
            "    on t2.artical_id = t3.id\n" +
            "where t3.show_flag = #{showFlag}\n" +
            "group by t1.id\n" +
            "order by count(1) desc limit #{count}")
    List<Map> selectTags(int count, String showFlag);

    @Select("select tag.id,tag.name,(select count(1) from articaltag where tag.id=articaltag.tag_id) as count from tag order by id asc")
    List<Map> listTop();

    @Select("select\n" +
            "  t1.id,\n" +
            "  t1.name,\n" +
            "  count(1) as count\n" +
            "from tag t1\n" +
            "  inner join articaltag t2\n" +
            "    on t1.id = t2.tag_id\n" +
            "  inner join artical t3\n" +
            "    on t2.artical_id = t3.id\n" +
            "where t3.show_flag = #{showFlag}\n" +
            "group by t1.id\n" +
            "order by count(1) desc")
    List<Map> selectTagsByShowFlag(String showFlag);
}
